CREATE TABLE LOAI_CHI
(
	ID INT PRIMARY KEY IDENTITY(1,1), 
	NAME NVARCHAR(200), 
	CREATE_BY INT FOREIGN KEY REFERENCES USR(ID), 
	UPDATE_BY  INT FOREIGN KEY REFERENCES USR(ID),  
	CREATE_AT DATE, 
	UPDATE_AT DATE,
	ACTIVE CHAR(1)
)

CREATE PROC SP_GET_LOAI_CHI
@ID INT
AS
BEGIN
	SELECT ID, NAME
	FROM LOAI_CHI
	WHERE ACTIVE = 'A' AND (@ID = 0 OR ID = @ID)
END

ALTER TABLE CHI_TIEU
ADD
LOAI_CHI INT FOREIGN KEY REFERENCES LOAI_CHI(ID)


ALTER PROC [dbo].[SP_INSERT_CHI_TIEU]
@MUC_DICH NVARCHAR(200), 
@SO_TIEN FLOAT, 
@NGUOI_CHI INT, 
@NGAY_CHI DATE, 
@SO_THUE_PHONG INT, 
@LOAI_CHI INT,
@NOTES NTEXT,
@USR_ID INT
AS
BEGIN
	INSERT INTO CHI_TIEU
	(
		MUC_DICH,
		SO_TIEN,
		NGUOI_CHI,
		NGAY_CHI,
		SO_THUE_PHONG,
		LOAI_CHI,
		NOTES,
		CREATE_BY,
		CREATE_AT,
		ACTIVE
	)
	VALUES
	(
		@MUC_DICH, 
		@SO_TIEN, 
		@NGUOI_CHI, 
		@NGAY_CHI, 
		CASE @SO_THUE_PHONG 
		WHEN 0 THEN
		NULL
		ELSE @SO_THUE_PHONG END, 
		@LOAI_CHI,
		@NOTES,
		@USR_ID, 
		GETDATE(),
		'A'
	)
END


ALTER PROC [dbo].[SP_UPDATE_CHI_TIEU]
@ID INT,
@MUC_DICH NVARCHAR(200), 
@SO_TIEN FLOAT, 
@NGAY_CHI DATE, 
@SO_THUE_PHONG INT, 
@LOAI_CHI INT,
@NOTES NTEXT,
@USR_ID INT
AS
BEGIN
	UPDATE CHI_TIEU
	SET MUC_DICH = @MUC_DICH, 
	SO_TIEN = @SO_TIEN, 
	NGAY_CHI = @NGAY_CHI, 
	LOAI_CHI = @LOAI_CHI,
	SO_THUE_PHONG = 
	CASE @SO_THUE_PHONG 
	WHEN 0 THEN 
		 NULL
	ELSE @SO_THUE_PHONG END, 
	UPDATE_AT = GETDATE(), 
	UPDATE_BY = @USR_ID, 
	NOTES = @NOTES
	WHERE  ID = @ID
END


ALTER PROC [dbo].[SP_GET_CHI_TIEU]
@NGUOI_CHI INT, 
@NGAY_CHI_FROM DATE, 
@NGAY_CHI_TO DATE,
@SO_THUE_PHONG INT, 
@LOAI_CHI INT,
@TINH_TRANG INT 
AS
BEGIN
	SELECT 
		CHI_TIEU.ID, 
		MUC_DICH, 
		ISNULL(SO_TIEN,0) SO_TIEN, 
		NGUOI_CHI, 
		NGAY_CHI,
		USR.FULL_NAME, 
		PHONG.SO_PHONG, 
		KHACH_THUE.HO_VA_TEN, 
		CHI_TIEU.MA_PHIEU_THU, 
		CHI_TIEU.LOAI_CHI,
		LOAI_CHI.NAME
	FROM 
		CHI_TIEU
		LEFT JOIN LOAI_CHI ON CHI_TIEU.LOAI_CHI = LOAI_CHI.ID
		LEFT JOIN THUE_PHONG ON CHI_TIEU.SO_THUE_PHONG = THUE_PHONG.ID
		LEFT JOIN PHONG ON THUE_PHONG.MA_PHONG = PHONG.ID
		LEFT JOIN KHACH_THUE ON KHACH_THUE.SO_THUE_PHONG = THUE_PHONG.ID AND KHACH_THUE.LEVEL = 1
		JOIN USR ON CHI_TIEU.NGUOI_CHI = USR.ID
	WHERE 
		    (@NGUOI_CHI = 0 OR CHI_TIEU.NGUOI_CHI = @NGUOI_CHI)
		AND (@LOAI_CHI = 0 OR CHI_TIEU.LOAI_CHI = @LOAI_CHI)
		AND (@NGAY_CHI_FROM IS NULL OR CAST(CHI_TIEU.NGAY_CHI AS DATE) >= CAST(@NGAY_CHI_FROM AS DATE))
		AND (@NGAY_CHI_TO IS NULL OR CAST(CHI_TIEU.NGAY_CHI AS DATE) <= CAST(@NGAY_CHI_TO AS DATE))
		AND (@SO_THUE_PHONG = -1 OR (@SO_THUE_PHONG = 0 AND CHI_TIEU.SO_THUE_PHONG IS NOT NULL) OR (CHI_TIEU.SO_THUE_PHONG = @SO_THUE_PHONG))
		AND ((@TINH_TRANG = 0 AND CHI_TIEU.ACTIVE IN ('A','C'))
				OR (@TINH_TRANG = 1 AND CHI_TIEU.ACTIVE = 'A' AND CHI_TIEU.SO_THUE_PHONG IS NOT NULL) 
				OR (@TINH_TRANG = 2 AND CHI_TIEU.ACTIVE = 'C' AND CHI_TIEU.SO_THUE_PHONG IS NOT NULL))
	ORDER BY NGAY_CHI DESC
END


ALTER PROC [dbo].[SP_GET_CHI_TIEU_BY_ID] 
@ID INT
AS
BEGIN
	SELECT 
		CHI_TIEU.ID, 
		CHI_TIEU.MUC_DICH, 
		CHI_TIEU.NGAY_CHI, 
		CHI_TIEU.NGUOI_CHI,
		CHI_TIEU.SO_THUE_PHONG, 
		CHI_TIEU.MA_PHIEU_THU, 
		CHI_TIEU.NOTES,
		USR.FULL_NAME, 
		CHI_TIEU.SO_TIEN, 
		CHI_TIEU.LOAI_CHI
	FROM 
	CHI_TIEU, 
	USR
	WHERE CHI_TIEU.ACTIVE = 'A'
	AND CHI_TIEU.NGUOI_CHI = USR.ID
END

INSERT INTO LOAI_CHI
(
	NAME ,   
	CREATE_AT , 
	UPDATE_AT ,
	ACTIVE
)
VALUES
(
	N'Tiền lương nhân viên', 
	GETDATE(),
	GETDATE(), 
	'A'
)



INSERT INTO LOAI_CHI
(
	NAME ,   
	CREATE_AT , 
	UPDATE_AT ,
	ACTIVE
)
VALUES
(
	N'Tiền điện', 
	GETDATE(),
	GETDATE(), 
	'A'
)


INSERT INTO LOAI_CHI
(
	NAME ,   
	CREATE_AT , 
	UPDATE_AT ,
	ACTIVE
)
VALUES
(
	N'Tiền nước', 
	GETDATE(),
	GETDATE(), 
	'A'
)


INSERT INTO LOAI_CHI
(
	NAME ,   
	CREATE_AT , 
	UPDATE_AT ,
	ACTIVE
)
VALUES
(
	N'Tiền truyền hình', 
	GETDATE(),
	GETDATE(), 
	'A'
)


INSERT INTO LOAI_CHI
(
	NAME ,   
	CREATE_AT , 
	UPDATE_AT ,
	ACTIVE
)
VALUES
(
	N'Tiền Internet', 
	GETDATE(),
	GETDATE(), 
	'A'
)


INSERT INTO LOAI_CHI
(
	NAME ,   
	CREATE_AT , 
	UPDATE_AT ,
	ACTIVE
)
VALUES
(
	N'Phí vệ sinh', 
	GETDATE(),
	GETDATE(), 
	'A'
)


INSERT INTO LOAI_CHI
(
	NAME ,   
	CREATE_AT , 
	UPDATE_AT ,
	ACTIVE
)
VALUES
(
	N'Chi phí bảo trì, sửa chửa trang thiết bị', 
	GETDATE(),
	GETDATE(), 
	'A'
)


INSERT INTO LOAI_CHI
(
	NAME ,   
	CREATE_AT , 
	UPDATE_AT ,
	ACTIVE
)
VALUES
(
	N'Chi tiêu khác', 
	GETDATE(),
	GETDATE(), 
	'A'
)



CREATE FUNCTION [dbo].[F_GET_MOTO_PRICE]()
RETURNS FLOAT
AS
BEGIN
RETURN(SELECT TOP 1 CAST(ISNULL(VAL,'0') AS FLOAT) FROM MASTER WHERE ACTIVE = 'A' AND NAME = 'MOTO_PRICE')
END


CREATE FUNCTION F_COUNT_MOTO
(
	@ID INT
)RETURNS INT
AS
BEGIN
	RETURN (SELECT COUNT(ID) FROM MOTO WHERE SO_THUE_PHONG = @ID AND ACTIVE = 'A')
END

ALTER PROC [dbo].[SP_GET_CONFIG]
AS
BEGIN
	SELECT 
	DBO.F_GET_CONFIG('ELE_PRICE') ELE_PRICE, 
	DBO.F_GET_CONFIG('WAT_PRICE') WAT_PRICE,
	DBO.F_GET_CONFIG('MOTO_PRICE') MOTO_PRICE,
	DBO.F_GET_CONFIG('EMAIL') EMAIL,
	DBO.F_GET_CONFIG('PASS') PASS
END

ALTER PROC [dbo].[SP_GET_PRICE_AND_COST]
@ID INT
AS
BEGIN
	SELECT 
	DBO.F_GET_OTHER_COST(@ID) OTHER_COST,
	DBO.F_GET_ELE_PRICE() ELE_PRICE,
	DBO.F_GET_WAT_PRICE() WAT_PRICE,
	DBO.F_GET_MOTO_PRICE() MOTO_PRICE,
	DBO.F_COUNT_MOTO(@ID) MOTO_COUNT,
	DBO.F_GET_ROOM_PRICE(@ID) ROOM_PRICE
END